from datetime import datetime
import time
import pandas as pd
import json
from db_conn import executeSql, get_redis, pd_conn_zfw, pd_conn_wfw


# 门店健康度汇总
def factoryHealthQuery(path, startTime, endTime):

    # 站点基本信息、防撞功能、机器功能
    def basicTable():
        # 获取POINT中点位对应的value值
        def get_point_value(json_str):
            loads = json.loads(json_str)
            if "value" in loads:
                return loads['value']
            return None

        # 判断防撞杆是否合格（除外部机）
        def check_touch(station_num, box_uid):
            touch_points = [
                "传感器检测-左前下防撞胶条", "传感器检测-右前下防撞胶条",   # [:2]
                "传感器检测-左后下防撞胶条", "传感器检测-右后下防撞胶条",   # [2:4]
                "传感器检测-左后上防撞胶条", "传感器检测-右后上防撞胶条",   # [4:6]
                "传感器检测-左后竖防撞胶条", "传感器检测-右后竖防撞胶条",   # [6:8]
                "传感器检测-左前竖防撞胶条", "传感器检测-右前竖防撞胶条",   # [8:10]
                "传感器检测-行人防撞胶条",  "传感器检测-柜门防撞胶条",    # [10:12]

                "防撞功能设置-左前下防撞胶条功能", "防撞功能设置-右前下防撞胶条功能",     # [12:14]
                "防撞功能设置-左后下防撞胶条功能", "防撞功能设置-右后下防撞胶条功能",     # [14:16]
                "防撞功能设置-左后上防撞胶条功能", "防撞功能设置-右后上防撞胶条功能",     # [16:18]
                "防撞功能设置-左后竖防撞胶条功能", "防撞功能设置-右后竖防撞胶条功能",     # [18:20]
                "防撞功能设置-左前竖防撞胶条功能", "防撞功能设置-右前竖防撞胶条功能",     # [20:22]
                "防撞功能设置-行人防撞胶条功能",  "防撞功能设置-柜门防撞胶条功能"       # [22:]
            ]

            # 1-6
            touch_set_1_6 = touch_points[12:22]
            touch_sense_1_6 = touch_points[:10]

            # 6se
            touch_set_6se = touch_points[18:]
            touch_sense_6se = touch_points[6:12]

            # 愿景7
            touch_set_7 = touch_points[20:23]
            touch_sense_7 = touch_points[8:11]

            # 隧道机
            touch_set_8 = touch_points[12:]
            touch_sense_8 = touch_points[:12]

            point_json = redis.hgetall("FBOX_POINT_%s" % box_uid)

            if '功能按钮-防碰撞功能开启' in point_json:
                main_switch_value = get_point_value(point_json['功能按钮-防碰撞功能开启'])
            else:
                main_switch_value = None

            # 防碰撞功能开启点位关闭，不合格，1-5代机无点位，不合格（需豪杰核查）
            if main_switch_value == '0' or (main_switch_value is None and station_num in (1, 2, 3, 4, 5)):
                return False
            # 防碰撞功能开启或无点位（7可无此点位）
            else:
                # 1-5代机，传感器点位缺失，不合格
                if station_num in (1, 2, 3, 4, 5):
                    for sense in touch_sense_1_6:
                        if sense not in point_json:
                            return False
                # 6-7代机，按照指定防撞杆和传感器，任一缺失，不合格，防撞杆出现关闭，不合格
                elif station_num == 6:
                    # 遍历每对防撞传感器和功能设置
                    for sense, set in zip(touch_sense_1_6, touch_set_1_6):
                        if set in point_json:
                            set_value = get_point_value(point_json[set])
                            # 防撞功能出现关闭，不合格
                            if set_value == '0':
                                return False
                        else:
                            # 功能设置缺失，不合格
                            return False
                        # 传感器缺失，不合格
                        if sense not in point_json:
                            return False
                elif station_num == 6001 or station_num == 6002:
                    # 遍历每对防撞传感器和功能设置
                    for sense, set in zip(touch_sense_6se, touch_set_6se):
                        if set in point_json:
                            set_value = get_point_value(point_json[set])
                            # 防撞功能出现关闭，不合格
                            if set_value == '0':
                                return False
                        else:
                            # 功能设置缺失，不合格
                            return False
                        # 传感器缺失，不合格
                        if sense not in point_json:
                            return False
                elif station_num == 6003 or station_num == 6004:
                    # 遍历每对防撞传感器和功能设置
                    for sense, set in zip(touch_sense_7, touch_set_7):
                        if set in point_json:
                            set_value = get_point_value(point_json[set])
                            # 防撞功能出现关闭，不合格
                            if set_value == '0':
                                return False
                        else:
                            # 功能设置缺失，不合格
                            return False
                        # 传感器缺失，不合格
                        if sense not in point_json:
                            return False
                # 隧道机，有多少对判断多少对
                else:
                    # 遍历每对防撞传感器和功能设置
                    for sense, set in zip(touch_sense_8, touch_set_8):
                        if set in point_json:
                            set_value = get_point_value(point_json[set])
                            # 防撞功能出现关闭，不合格
                            if set_value == '0':
                                return False
                        else:
                            set_value = None

                        if sense in point_json:
                            sense_value = get_point_value(point_json[sense])
                        else:
                            sense_value = None

                        #  每对传感器、防撞功能二缺一，不合格
                        if (set_value is None and sense_value is not None) or (
                                set_value is not None and sense_value is None):
                            return False
                # 防碰撞功能开启或无点位下，其余情况合格
                return True

        # 判断功能按钮是否合格（除外部机）
        def check_button(station, box):

            func_button = ["功能按钮-水压检测功能", "功能按钮-超高检测功能", "功能按钮-轮毂屏蔽功能"]
            sense_button = [["传感器检测-循环水压信号", "传感器检测-缺水保护"], "传感器检测-超高", ["传感器检测-左轮刷开位", "传感器检测-右轮刷开位"]]

            point_json_key = "FBOX_POINT_%s" % box
            point_json = redis.hgetall(point_json_key)

            i = 0
            for button, sense in zip(func_button, sense_button):
                i += 1
                # 水压
                if i == 1:
                    if button in point_json:
                        button_value = get_point_value(point_json[button])
                    else:
                        button_value = None
                    if sense[0] in point_json:
                        sense_value1 = get_point_value(point_json[sense[0]])
                    else:
                        sense_value1 = None
                    if sense[1] in point_json:
                        sense_value2 = get_point_value(point_json[sense[1]])
                    else:
                        sense_value2 = None
                    # 所有机型 按钮或传感器(6001/2/3/4判断sense2,其余判断sense1)出现缺失，不合格，按钮出现关闭，不合格
                    if station in (6001, 6002, 6003, 6004):
                        if button_value == '0' or button_value is None or sense_value2 is None:
                            return False
                    else:
                        if button_value == '0' or button_value is None or sense_value1 is None:
                            return False
                # 超高
                elif i == 2:
                    if button in point_json:
                        button_value = get_point_value(point_json[button])
                    else:
                        button_value = None
                    if sense in point_json:
                        sense_value = get_point_value(point_json[sense])
                    else:
                        sense_value = None
                    # 6,6001,6002,8001 按钮或传感器出现缺失，不合格，按钮出现关闭，不合格，其他机型不判断
                    if station in (6, 6001, 6002, 8001):
                        if button_value == '0' or button_value is None or sense_value is None:
                            return False
                # 轮毂屏蔽
                else:
                    if button in point_json:
                        button_value = get_point_value(point_json[button])
                    else:
                        button_value = None
                    if sense[0] in point_json:
                        sense_value1 = get_point_value(point_json[sense[0]])
                    else:
                        sense_value1 = None
                    if sense[1] in point_json:
                        sense_value2 = get_point_value(point_json[sense[1]])
                    else:
                        sense_value2 = None
                    # 所有机型 轮毂关闭，不合格，传感器/按钮有缺失，不合格
                    if button_value == '1' or button_value is None or sense_value1 is None or sense_value2 is None:
                        return False
            return True

        def get_fact_info():
            fact_query = """
                select f_id,f_name,f_boxUid,f_work_start_time,f_province,f_city,f_district,f_business_category,f_station_num
                ,case 
                    when f_station_num <= 6 then '愿景' when f_station_num = 10 then '阿尔法' 
                    when f_station_num = 6004 then '愿景LITE 轮刷版' when f_station_num = 6001 then '愿景6SE1' 
                    when f_station_num = 6002 then '愿景6SE2' when f_station_num = 6003 then '愿景LITE 高压水版' 
                    when f_station_num = 10001 then '杭州太仆洗车机' when f_station_num = 10002 then '麦迪斯洗车机' 
                    when f_station_num = 10003 then '安露洗车机' when f_station_num = 8001 then '隧道机' 
                    when f_station_num = 8002 then '灵动洗车机' else f_station_num end
                ,case f_skill_score 
                    when 1 then '自营' when 2 then '油站代理' when 3 then '社区代理' 
                    when 4 then '买断' when 5 then '收益平分' end f_skill_score
                from t_factory where f_status = 0 and f_boxUid is not null 
            """
            return executeSql(fact_query)

        fact_title, fact_content = get_fact_info()
        redis = get_redis()

        # 外部洗车机
        inner_station = (1, 2, 3, 4, 5, 6, 6001, 6002, 6003, 6004, 8001)
        fact_info_list = []
        # fact_content = fact_content[:10]
        for fact in fact_content:
            # print(fact[0])
            station_num = fact[-3]
            box_uid = fact[2]
            status_json_str = redis.hgetall("MACHINE_STATUS_%s" % box_uid)
            if '是否在线' in status_json_str:
                status = status_json_str['是否在线']
            else:
                status = None

            # 外部机、盒子不在线(value=1) 防撞、清洗、泡沫水蜡 都合格
            if (station_num not in inner_station) or (status == '1') or (status is None):
                fact_info_list.append(fact + (True, True, True))
            # 其余盒子在线机器 防撞、清洗 用方法单独判断 泡沫水蜡 空
            else:
                fact_info_list.append(fact + (check_touch(station_num, box_uid), check_button(station_num, box_uid), None))

        df_fact = pd.DataFrame(fact_info_list)

        del df_fact[2]
        df_fact.columns = ['站点ID', '站点名称', "开业时间", "省", "市", "区", "业务类型", "机器版本编号", "机器版本", "站点性质", '防撞功能', '清洗功能', '泡沫水水蜡']
        df_fact.replace(True, '合格', inplace=True)
        df_fact.replace(False, '不合格', inplace=True)
        df_basic_table = df_fact.set_index('站点ID')

        return df_basic_table

    # 泡沫水水蜡
    def foamWax():
        """泡沫和水蜡的值都在 1-110 之间算合格"""
        sql = """
            select t1.*, if(wax>0 and wax<=110 and water>0 and water<=110, 1, 0) 'foam_wax'
            from (
                SELECT f.f_id '站点ID',f_wax_water wax,f_foam_water water 
                FROM t_factory_parameter fp
                join t_factory f on fp.f_factory_id = f.f_id
                where f.f_status = 0
            ) t1
        """
        df = pd_conn_zfw(sql)
        df.foam_wax.replace(1, '合格', inplace=True)
        df.foam_wax.replace(0, '不合格', inplace=True)
        df_foam_wax = df[['站点ID', 'foam_wax']]
        df_foam_wax = df_foam_wax.set_index('站点ID')
        return df_foam_wax

    # 摄像头识别率
    def cameraRecognitionRate():
        sql = """
            SELECT
                o.f_factory_id '站点ID'
                , if(s2.f_camera2_index=1,'在线',if(s2.f_camera2_index=0,'不在线','无')) '车顶在线'
                , if(s3.f_camera2_index=1,'在线',if(s3.f_camera2_index=0,'不在线','无')) '车尾在线'
                , count(if(t1.f_type=1, t1.f_auto_no, null)) '车顶识别'
                , count(if(t1.f_type=0, t1.f_auto_no, null)) '车尾识别'
                , count(t1.f_order_id) '图片获取总数'
                , count(o.f_order_id) '订单总数'
                , count(if(t1.f_type=1, t1.f_auto_no, null))/count(o.f_order_id) '车顶识别率'
                , count(if(t1.f_type=0, t1.f_auto_no, null))/count(o.f_order_id) '车尾识别率'
                , count(t1.f_auto_no)/count(o.f_order_id) '总单识别率'
            FROM
                t_order o
                left join t_factory_station s2 on o.f_factory_id=s2.f_factory_id and s2.f_camera_type='T'
                left join t_factory_station s3 on o.f_factory_id=s3.f_factory_id and s3.f_camera_type='B'
                left join t_order_car_record t1 on t1.f_order_id=o.f_order_id
            WHERE o.f_order_date >= '%s' and o.f_order_date < '%s' and o.f_status>0
            GROUP BY o.f_factory_id
        """ % (startTime, endTime)
        df = pd_conn_zfw(sql)
        df.loc[(df.订单总数 == 0) & (df.车顶在线 == '无') & (df.车尾在线 == '无'), '总单识别率'] = 0
        df.fillna({"总单识别率": 1}, inplace=True)
        df.loc[(df.总单识别率 >= 0.6), '摄像头识别率'] = '合格'
        df.loc[(df.总单识别率 < 0.6), '摄像头识别率'] = '不合格'
        df_camera_recognition_rate = df[['站点ID', '摄像头识别率']]
        df_camera_recognition_rate = df_camera_recognition_rate.set_index('站点ID')
        return df_camera_recognition_rate

    # C端评价
    def customerComment():
        sql = """
            SELECT t2.f_id '站点ID',
                count(if(t1.f_sa_score2 >3,1,null)) '清洁',
                count(if(t1.f_sa_score3 >3,1,null)) '环境',
                count(if(t1.f_sa_score4 >3 ,1,null)) '设备'
            from t_order_comment t1
            LEFT JOIN t_factory t2 on t1.f_factory_id = t2.f_id
            where t1.f_indate >= '%s' and t1.f_indate < '%s' and t1.f_status = 0 and t2.f_status = 0
            group by t2.f_id
        """ % (startTime, endTime)
        df = pd_conn_zfw(sql)
        df.loc[(df.清洁 > 0), "C端评价清洁"] = "合格"
        df.loc[(df.环境 > 0), "C端评价环境"] = "合格"
        df.loc[(df.设备 > 0), "C端评价设备"] = "合格"
        df.loc[(df.清洁 == 0), "C端评价清洁"] = "不合格"
        df.loc[(df.环境 == 0), "C端评价环境"] = "不合格"
        df.loc[(df.设备 == 0), "C端评价设备"] = "不合格"
        df_customer_comment = df[['站点ID', 'C端评价清洁', "C端评价环境", "C端评价设备"]]
        df_customer_comment = df_customer_comment.set_index('站点ID')
        return df_customer_comment

    # B端评价
    def businessComment():
        """B端评价全五星，合格"""
        sql = """
            select f_factory_id '站点ID',count(1) 'total'
            , sum(if(f_content<>'5星',1,0)) 'not5'
            , if(sum(if(f_content<>'5星',1,0))=0,'合格','不合格') 'B端评价'
            from  t_question_answer a 
            left join t_question_answer_content ac on ac.f_question_answer_id = a.f_id
            where a.f_question_id = 1 and ac.f_type = 3
            and a.f_create_time >= '%s' and a.f_create_time < '%s'
            group by f_factory_id
        """ % (startTime, endTime)
        df = pd_conn_wfw(sql, 'ygl_task')
        df_business_comment = df[['站点ID', 'B端评价']]
        df_business_comment = df_business_comment.set_index('站点ID')
        return df_business_comment

    # 清洗完成率
    def cleaningRate():
        sql = f"""
            select 
                fid '站点ID', 
                1 - count(aid) / count(oid) 'clean'
            from (	
                select 
                    o.f_factory_id fid, o.f_order_id 'oid', a.f_order_id 'aid' 
                from (
                    select f_factory_id,f_mobile,f_order_id
                    from t_order where f_order_date >= '{startTime}' and f_order_date < '{endTime}' and f_status > 0
                ) o
                left join t_washcar_alarm a on a.f_order_id = o.f_order_id 
                    and f_alarm_name not in (
                        "右后竖防撞胶条触发","左后竖防撞胶条触发","右前竖防撞胶条触发","左前竖防撞胶条触发","龙门前进碰到防撞杆","右前下防撞胶条触发",
                        "右后上防撞胶条触发","左前下防撞胶条触发","左后上防撞胶条触发","左后下防撞胶条触发","柜门防撞胶条触发","龙门后退碰到防撞杆",
                        "行人防撞胶条触发","右后下防撞胶条触发","用户使用手机APP停止","洗车机上急停按钮按下",      -- 客户人为原因报警
                        "水箱缺水","水系统跳闸或膜堵塞","请增加MBR膜数量","泡沫水压低","通讯异常-顶刷旋转","轮毂检测异常，跳过轮毂",
                        "集水井溢水","气压低报警","水压过大","左刷高低位异常","变频器异常","光电A被遮挡","正常归位超时","光电B被遮挡",
                        "防冻排水已完成","风机轮子报错","风机状态掉线","洗车机雷达掉线","引导雷达状态掉线","顶刷旋转电流超高","污水高液位",
                        "工作区防追尾被遮挡","风机3&4变频器故障","洗车机背门未关好","洗车机轮子")        -- 不停机报警
                group by o.f_order_id
            ) t1
            group by fid;
        """
        df = pd_conn_zfw(sql)
        df.loc[df.clean < 0.95, '清洗完成率'] = '不合格'
        df.fillna({"清洗完成率": '合格'}, inplace=True)
        df_cleaning_rate = df[['站点ID', '清洗完成率']]
        df_cleaning_rate = df_cleaning_rate.set_index('站点ID')
        return df_cleaning_rate

    # 月停机时间
    def monthInterruptTime():
        """
        月停机时间（维护类型不包含霜冻预警，站点停水，站点停电，自然灾害，油站双层罐改造，剔除<2h维护时长记录）总和大于2天不合格
        """
        sql = f"""
            select t1.f_factory_id '站点ID',sum(t1.f_timeDiff)/60/60/24 'totalday'
            from (
                select f_factory_id, TIMESTAMPDIFF(SECOND, f_startTime, ifnull(f_endTime, '{endTime}')) f_timeDiff
                from t_factory_business
                where
                    f_startTime < '{endTime}' and ( f_endTime >= '{startTime}' or f_endTime is null )
                    and f_work_status not in (5,11,12,13,15)
                    and TIMESTAMPDIFF(SECOND, f_startTime, ifnull(f_endTime, '{endTime}'))/60/60 >= 2
            ) t1
            group by t1.f_factory_id
        """
        df = pd_conn_zfw(sql)
        df.loc[df.totalday > 2, '月停机时间'] = '不合格'
        df.loc[df.totalday <= 2, '月停机时间'] = '合格'
        df_month_interrupt_time = df[['站点ID', '月停机时间']]
        df_month_interrupt_time = df_month_interrupt_time.set_index('站点ID')
        return df_month_interrupt_time

    # 月维护次数
    def monthRepairSequence():
        """
        月维护次数（故障抢修，现场检修）两次以上不合格
        """
        sql = f"""
            select f_factory_id '站点ID', count(1) 'num'
            from t_factory_business
            where
                f_startTime < '{endTime}' and ( f_endTime >= '{startTime}' or f_endTime is null )
                and f_work_status in (1,8)
                and TIMESTAMPDIFF(SECOND, f_startTime, ifnull(f_endTime, '{endTime}'))/60/60 >= 2
            group by f_factory_id
        """
        df = pd_conn_zfw(sql)
        df.loc[df.num > 2, '月维护次数'] = '不合格'
        df.loc[df.num <= 2, '月维护次数'] = '合格'
        df_month_repair_sequence = df[['站点ID', '月维护次数']]
        df_month_repair_sequence = df_month_repair_sequence.set_index('站点ID')
        return df_month_repair_sequence

    # 月维护天数
    def monthRepairTime():
        """
        月维护时长（故障抢修，现场检修）24h以上不合格
        """
        sql = f"""
            select t1.f_factory_id '站点ID',sum(t1.f_timeDiff)/60/60/24 'totalday'
            from (
                select f_factory_id, TIMESTAMPDIFF(SECOND, f_startTime, ifnull(f_endTime, '{endTime}')) f_timeDiff
                from t_factory_business
                where
                    f_startTime < '{endTime}' and ( f_endTime >= '{startTime}' or f_endTime is null )
                    and TIMESTAMPDIFF(SECOND, f_startTime, ifnull(f_endTime, '{endTime}'))/60/60 >= 2
                    and f_work_status in (1,8)
            ) t1
            group by t1.f_factory_id 
        """
        df = pd_conn_zfw(sql)
        df.loc[df.totalday > 1, '月维护时长'] = '不合格'
        df.loc[df.totalday <= 1, '月维护时长'] = '合格'
        df_month_repair_time = df[['站点ID', '月维护时长']]
        df_month_repair_time = df_month_repair_time.set_index('站点ID')
        return df_month_repair_time

    # 合并表格
    def dfs_concat():
        print('门店健康度评价导出中...')
        # print('泡沫水水蜡、摄像头识别率、C端评价、B端评价、清洗完成度、月停机时长、月维护次数、月维护时长正在生成...')
        tt1 = datetime.now()
        df_1 = foamWax()
        tt2 = datetime.now()
        print('泡沫水水蜡执行时长', tt2-tt1)
        df_2 = cameraRecognitionRate()
        tt3 = datetime.now()
        print('摄像头识别率执行时长', tt3-tt2)
        df_3 = customerComment()
        tt4 = datetime.now()
        print('C端评价执行时长', tt4-tt3)
        df_4 = businessComment()
        tt5 = datetime.now()
        print('B端评价执行时长', tt5-tt4)
        df_5 = cleaningRate()
        tt6 = datetime.now()
        print('清洗完成度执行时长', tt6-tt5)
        df_6 = monthInterruptTime()
        tt7 = datetime.now()
        print('月停机时长执行时长', tt7-tt6)
        df_7 = monthRepairSequence()
        tt8 = datetime.now()
        print('月维护次数执行时长', tt8-tt7)
        df_8 = monthRepairTime()
        tt9 = datetime.now()
        print('月维护时长执行时长', tt9-tt8)

        # 合并除基础表外的所有表
        df_concat1 = pd.concat([df_1, df_2, df_3, df_4, df_5, df_6, df_7, df_8], axis=1, join='outer')
        print('防撞杆、清洗功能正在生成...')
        # 以基础表站点为准合并->基础表包含防撞杆、清洗功能
        df_concat2 = pd.merge(basicTable(), df_concat1, how='left', on='站点ID')
        tt10 = datetime.now()
        print('防撞杆、清洗功能执行时长', tt10 - tt9)
        df_concat2.loc[df_concat2['泡沫水水蜡'].isna(), '泡沫水水蜡'] = df_concat2.loc[df_concat2['泡沫水水蜡'].isna(), 'foam_wax']
        # col = df_concat2.columns
        col = ['站点名称', '开业时间', '省', '市', '区', '业务类型', '机器版本编号', '机器版本', '站点性质', '泡沫水水蜡',
               '摄像头识别率', 'C端评价清洁', 'C端评价环境', 'C端评价设备', 'B端评价', '清洗完成率', '月停机时间', '月维护次数',
               '月维护时长', '防撞功能', '清洗功能']
        df_total = df_concat2.loc[:, col]
        df_total.fillna({'泡沫水水蜡': '不合格'}, inplace=True)
        df_total.fillna('合格', inplace=True)
        df_total['水电能耗'] = None
        df_total['线上巡检'] = None
        df_total['风控监察'] = None
        df_total.loc[(df_total['机器版本编号'] == 10) | (df_total['机器版本编号'] == 8002), ['泡沫水水蜡',
                                                                                 '摄像头识别率', 'C端评价清洁', 'C端评价环境', 'C端评价设备',
                                                                                 'B端评价', '清洗完成率', '月停机时间',
                                                                                 '月维护次数',
                                                                                 '月维护时长', '防撞功能', '清洗功能']] = '合格'
        print('门店健康度已导出完毕，期待您的下次光临！')
        return df_total

    title_time1 = startTime[-5:-3] + startTime[-2:]
    title_time2 = endTime[-5:-3] + endTime[-2:]
    title_time3 = datetime.strftime(datetime.now(), '%H%M%S')

    return dfs_concat().to_excel(r'{}\门店健康度{}~{}({}).xlsx'.format(path, title_time1, title_time2, title_time3))


if __name__ == '__main__':

    # 下载路径 D:\Download\python丽莎
    path = r'D:\Download\python丽莎'
    # 开始日期（包含）
    startTime = '2022-04-01'
    # 结束日期（不包含）
    endTime = '2022-04-02'

    factoryHealthQuery(path, startTime, endTime)

